package jdbc.dao;

import jdbc.domian.User;
import jdbc.utils.JDBCUtils;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by hasee on 2017/9/11.
 */
public class UserDao{

    String sql = null;

    public List<User> findAll(){
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps=null;
        ResultSet set=null;
        LinkedList<User> list = new LinkedList();
        try {
            sql="select * from user";
            ps = conn.prepareStatement(sql);
            set = ps.executeQuery(sql);
            while (set.next()){
                User user = new User();
                user.setId(set.getInt("id"));
                user.setUsername(set.getString("username"));
                user.setPassword(set.getString("password"));
                user.setEmail(set.getString("email"));
                user.setPhone(set.getString("phone"));
                list.add(user);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (set!=null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public boolean insert(User user){
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into user (id,username,password,email,phone) values (?,?,?,?,?)";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getEmail());
            ps.setString(5,user.getPhone());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtils.close();
        }
        return false;
    }

    public boolean update(User user){
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps=null;
        String sql="UPDATE USER SET username=?,password=?,email=?,phone=? WHERE id=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getEmail());
            ps.setString(4,user.getPhone());
            ps.setInt(5,user.getId());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtils.close();
        }
        return false;
    }

    public boolean delete(int id){
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps =null;
        String sql="DELETE FROM USER WHERE id=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtils.close();
        }
        return false;
    }
    public User findById(int id){
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps=null;
        ResultSet set=null;
        try {
            sql="select * from user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            set = ps.executeQuery();
            while (set.next()){
                User user = new User();
                user.setId(set.getInt("id"));
                user.setUsername(set.getString("username"));
                user.setPassword(set.getString("password"));
                user.setEmail(set.getString("email"));
                user.setPhone(set.getString("phone"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (set!=null){
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtils.close();
        }
        return null;
    }
}
